/***********************************************************************************************************************************
 *    Name: Create_Snapshot.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
:SETVAR SnapshotName "BBA$Close"
DECLARE
  @DataDir	NVARCHAR(255)	= master.DBAdmin.GetInstancePath(N'DefaultData')+N'$(SnapshotName)\',
  @TLogDir	NVARCHAR(255)	= master.DBAdmin.GetInstancePath(N'DefaultLog' )+N'$(SnapshotName)\',
  @tsql		NVARCHAR(MAX)	= N'CREATE DATABASE [$(SnapshotName)] ON ';

SELECT @tsql+= N'(NAME=' + name + N', FILENAME=''' + @DataDir + name + N'.ss''),' FROM sys.database_files WHERE type = 0 ORDER BY data_space_id;
SET @tsql= LEFT(@tsql, LEN(@tsql)-1) + N' AS SNAPSHOT OF [' + DB_NAME() + N'];';

EXECUTE (@tsql);
GO